﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace MountSilverLibraryRemake
{
    public partial class AccessSale : Form
    {
        private easySorter sorter;
        String adminString;
        public AccessSale(ref Trans trans)
        {
            InitializeComponent();
            this.adminString = trans.getUsernameTrans();
            sorter = new easySorter();
            this.saleStatus.ListViewItemSorter = sorter;
            sorter.SortOrder = SortOrder.Ascending;
        }
        public void flushSaleStatus()
        {
            saleStatus.Items.Clear();
            String[] ColumnsHeaderString = { "mode","ISBN", "number","finalprice","date","admin" };
            String loginConnection = "server=localhost;user id=root;password=root;database=mountsilverlibraryremake";
            MySqlConnection connectionLogin = new MySqlConnection(loginConnection);
            try
            {
                connectionLogin.Open();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            String saleCommand = "select * from sale";
            MySqlCommand commandLogin = new MySqlCommand(saleCommand, connectionLogin);
            MySqlDataReader saledr = commandLogin.ExecuteReader();
            while (saledr.Read())
            {
                ListViewItem listToAdd = new ListViewItem();
                listToAdd.Text = saledr[ColumnsHeaderString[0]].ToString();
                for (int i = 1; i < saleStatus.Columns.Count; i++)
                {
                    listToAdd.SubItems.Add(saledr[ColumnsHeaderString[i]].ToString());
                }
                saleStatus.Items.Add(listToAdd);
            }
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
        }
        private void AccessSale_Load(object sender, EventArgs e)
        {
            flushSaleStatus();
        }

        private void saleStatus_ColumnClick(object sender, ColumnClickEventArgs e)
        {
            if (e.Column == this.sorter.SortColumn)
            {
                if (this.sorter.SortOrder == SortOrder.Ascending)
                    this.sorter.SortOrder = SortOrder.Descending;
                else
                if (this.sorter.SortOrder == SortOrder.Descending)
                    this.sorter.SortOrder = SortOrder.Ascending;
                else
                    return;
            }
            else
            {
                this.sorter.SortColumn = e.Column;
            }
            this.saleStatus.Sort();
        }

        private void 增加记录ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            bool isConfirmed = true;
            int remain;
            TrueSale trueSale = new TrueSale();
            Form tableForm = new AddAndModifyDialogBlue(ref trueSale);
            tableForm.ShowDialog();
            String loginConnection = "server=localhost;user id=root;password=root;database=mountsilverlibraryremake";
            MySqlConnection connectionLogin = new MySqlConnection(loginConnection);
            try
            {
                connectionLogin.Open();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            String confirm = string.Format("select * from library where ISBN='{0}'", trueSale.ISBN);
            MySqlCommand confirmCommand = new MySqlCommand(confirm, connectionLogin);
            MySqlDataReader confirmrd = confirmCommand.ExecuteReader();
            while (confirmrd.Read())
            {
                isConfirmed = false;
            }
            remain = Convert.ToInt32(confirmrd["storage"]);
            confirmrd.Close();
            if (isConfirmed)
            {
                if (trueSale.mode == "入库")
                {
                    String libCommand = string.Format("update library set storage={0} where ISBN='{1}'", remain + trueSale.number, trueSale.ISBN);
                    MySqlCommand commandLib = new MySqlCommand(libCommand, connectionLogin);
                    commandLib.ExecuteReader();
                    String saleCommand = string.Format("insert into sale(mode,ISBN,number,finalprice,date,admin) " +
                "values('{0}','{1}',{2},{3},'{4}','{5}')", trueSale.mode, trueSale.ISBN, trueSale.number, trueSale.finalprice, trueSale.date.ToString(), adminString);
                    MySqlCommand commandSale = new MySqlCommand(saleCommand, connectionLogin);
                    commandSale.ExecuteNonQuery();
                }
                else
                {
                    if (remain >= trueSale.number)
                    {
                        String libCommand = string.Format("update library set storage={0} where ISBN='{1}'", remain - trueSale.number, trueSale.ISBN);
                        MySqlCommand commandLib = new MySqlCommand(libCommand, connectionLogin);
                        commandLib.ExecuteReader();
                        String saleCommand = string.Format("insert into sale(mode,ISBN,number,finalprice,date,admin) " +
                "values('{0}','{1}',{2},{3},'{4}','{5}')", trueSale.mode, trueSale.ISBN, trueSale.number, trueSale.finalprice, trueSale.date.ToString(), adminString);
                        MySqlCommand commandSale = new MySqlCommand(saleCommand, connectionLogin);
                        commandSale.ExecuteNonQuery();
                    }
                    else
                    {
                        MessageBox.Show("对应编号书目不足出库");
                    }
                }
            }
            else
            {
                MessageBox.Show("书库中没有操作的书目相关信息");
            }
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            flushSaleStatus();
        }

        private void 删除记录ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            String loginConnection = "server=localhost;user id=root;password=root;database=mountsilverlibraryremake";
            MySqlConnection connectionLogin = new MySqlConnection(loginConnection);
            try
            {
                connectionLogin.Open();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            for (int i = 0; i < saleStatus.SelectedItems.Count; i++)
            {
                String saleCommand = "delete from sale where `ISBN`=" + "\'" + saleStatus.SelectedItems[i].SubItems[1].Text + "\'";
                MySqlCommand commandSale = new MySqlCommand(saleCommand, connectionLogin);
                commandSale.ExecuteNonQuery();
            }
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
            flushSaleStatus();
        }

        private void 迭代查询ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form tableForm = new LimitForm(ref this.saleStatus);
            tableForm.Show();
        }

        private void 清除迭代条件ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            flushSaleStatus();
        }

        private void 生成时间区间报表ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DateTime datemin, datemax;
            datemin = datemax = System.DateTime.Now;
            Form tableForm = new FormTimePick(ref datemin,ref datemax);
            tableForm.ShowDialog();
            double sum = 0,insum=0,outsum=0,toadd=0;
            int count = 0;
            for(int i = 0; i < saleStatus.Items.Count; i++)
            {
                DateTime dateTo = Convert.ToDateTime(saleStatus.Items[i].SubItems[4]);
                if (dateTo < datemin || dateTo > datemax)
                {}
                else
                {
                    count++;
                    toadd= Convert.ToDouble(saleStatus.Items[i].SubItems[2]) * Convert.ToDouble(saleStatus.Items[i].SubItems[3]);
                    sum += toadd;
                    if (saleStatus.Items[i].SubItems[0].Equals("出库"))
                    {
                        insum += toadd;
                    }
                    else
                    {
                        outsum += toadd;
                    }
                }
            }
            String report = datemin.ToString() + "到" + datemax.ToString() + "之间一共有" + count + "笔交易,总流水:" + sum;
            report += "\n进账:" + insum + "\n出账:" + outsum + "\n总和:" + (insum - outsum);
            MessageBox.Show(report);
        }
    }
}
